package com.elomobile.harmony.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.elomobile.harmony.bean.exportPDAD.ExtractionBonTransport;
import com.elomobile.harmony.bean.generationElo.CleHarmony;
import com.elomobile.harmony.bean.generationElo.ExtractionVehiculeVoyage;
import com.elomobile.harmony.bean.generationElo.Tiers;
import com.elomobile.harmony.utils.UtilsBernardi;

public class RequetesDAO extends JdbcDaoSupport {

	/**
	 * Récupération de la liste des bons de transports à générer pour l'interface
	 */
	public ArrayList<ExtractionVehiculeVoyage> recuperationBonTransport(Logger log) {
		log.debug("Récupération des nouveaux bons de transport dans la table Harmony PDAD");
		ArrayList<ExtractionVehiculeVoyage> listeLigneVoyage = new ArrayList<ExtractionVehiculeVoyage>();
		ExtractionVehiculeVoyage vehicule = new ExtractionVehiculeVoyage();
		Connection conne = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			conne = getJdbcTemplate().getDataSource().getConnection();
			ps = conne.prepareStatement("SELECT * FROM PDAD WHERE PDTFT <> 1 and PDHIS <> 'H' and PDVEH <> 1 and PDVEH <> '????' and PDCD <> 'Q' order by PDVEH, PDNUM, PDREP, PDCD");
			rs = ps.executeQuery();
			int noDerVehicule = -1;
			ExtractionBonTransport ligne = null;
			int cpt = 0;
			while (rs.next()) {
				cpt++;
				int noVehiculeEnCours = rs.getInt("PDVEH");
				ligne = new ExtractionBonTransport();
				// Rupture sur le véhicule en cours.
				if (noVehiculeEnCours != noDerVehicule) {
					if (noDerVehicule > -1) {
						listeLigneVoyage.add(vehicule);
					}
					noDerVehicule = noVehiculeEnCours;
					vehicule = new ExtractionVehiculeVoyage();
					vehicule.setNoVehicule(noVehiculeEnCours);
				}

				ligne.setPddos(rs.getString("PDDOS").trim());
				ligne.setPdnum(rs.getString("PDNUM").trim());
				ligne.setPdrep(rs.getString("PDREP").trim());
				ligne.setPdNumSeq(rs.getString("PDNUMSEQ").trim());
				ligne.setPdcha(rs.getString("PDCHA").trim());
				ligne.setPdveh(rs.getString("PDVEH").trim());
				ligne.setPdms(rs.getString("PDMS").trim());
				ligne.setPddatt(rs.getString("PDDATT").trim());
				ligne.setPddat(rs.getString("PDDAT").trim());
				ligne.setPdpdu(rs.getString("PDPDU").trim());
				ligne.setPdcd(rs.getString("PDCD").trim());
				ligne.setPdqual(rs.getString("PDQUAL").trim());
				ligne.setPdinfon(rs.getString("PDINFON").trim());

				// Tiers enlèvement
				ligne.setPdcodee(rs.getString("PDCODEE").trim());
				ligne.setPdetse(rs.getString("PDETSE").trim());
				ligne.setPdnome(rs.getString("PDNOME").trim());
				ligne.setPdad1e(rs.getString("PDAD1E").trim());
				ligne.setPdad2e(rs.getString("PDAD2E").trim());
				ligne.setPdcpoe(rs.getString("PDCPOE").trim());
				ligne.setPdvile(rs.getString("PDVILE").trim());
				ligne.setPdcode(rs.getString("PDCODE").trim());
				ligne.setPdpaye(rs.getString("PDPAYE").trim());
				ligne.setPdtel(rs.getString("PDTEL").trim());
				ligne.setPdhrdv(rs.getString("PDHRDV").trim());
				ligne.setPdadcple(rs.getString("PDADCPLE").trim());

				// Tiers livraison
				ligne.setPdcoded(rs.getString("PDCODED").trim());
				ligne.setPdetsl(rs.getString("PDETSL").trim());
				ligne.setPdnoml(rs.getString("PDNOML").trim());
				ligne.setPdad1l(rs.getString("PDAD1L").trim());
				ligne.setPdad2l(rs.getString("PDAD2L").trim());
				ligne.setPdcpol(rs.getString("PDCPOL").trim());
				ligne.setPdvill(rs.getString("PDVILL").trim());
				ligne.setPdcodl(rs.getString("PDCODL").trim());
				ligne.setPdpayl(rs.getString("PDPAYL").trim());
				ligne.setPdtell(rs.getString("PDTELL").trim());
				ligne.setPdhrdvl(rs.getString("PDHRDVL").trim());
				ligne.setPdap(rs.getString("PDAP").trim());
				ligne.setPdadcpll(rs.getString("PDADCPLL").trim());

				ligne.setPdhrarr(rs.getString("PDHRARR").trim());
				ligne.setPdhrcharg(rs.getString("PDHRCHARG").trim());
				ligne.setPdhrdep(rs.getString("PDHRDEP").trim());

				ligne.setPdcomm(rs.getString("PDCOMM").trim());
				ligne.setPdncom(rs.getString("PDNCOM").trim());

				ligne.setPdcodec(rs.getString("PDCODEC").trim());
				ligne.setPdcaveh(rs.getString("PDCAVEH").trim());

				// 14/02/2011 ajout des adresses de passages à quaie (évolution demande par TRANSPORT bernardi)
				ligne.setPdnomet(rs.getString("PDNOMET").trim());
				ligne.setPdad1et(rs.getString("PDAD1ET").trim());
				ligne.setPdad2et(rs.getString("PDAD2ET").trim());
				ligne.setPdcpoet(rs.getString("PDCPOET").trim());
				ligne.setPdvilet(rs.getString("PDVILET").trim());

				// 14/02/2011 ajout du zone de commentaire plus grande (évolution demandé par TRANSPORT bernardi)
				ligne.setPdcomm2(rs.getString("PDCOMM2").trim());
				ligne.setPdcomm3(rs.getString("PDCOMM3").trim());

				vehicule.getListeLigneVoyage().add(ligne);
			}
			if (noDerVehicule > -1) {
				listeLigneVoyage.add(vehicule);
			}
		} catch (Throwable e) {
			log.error(e);
		} finally {
			try {
				conne.close();
			} catch (Exception e2) {
				log.error(e2);
			}

		}
		return listeLigneVoyage;
	}

	/***
	 * Méthode de mise d'un transfert OK.
	 * @param cle
	 */
	public void majTransfertOK(CleHarmony cle, Logger log) {
		try {
			getJdbcTemplate().update(
					"UPDATE PDAD set PDTFT = '1', PDETAT_TR = '0', PDCOD_TR = '0', PDLIB_TR = 'Envoyé à l interfaceElo', PDDAT_TR = '" + UtilsBernardi.recupereDateHeureSystemeNomFichier() + "'  WHERE PDDOS = '" + cle.getNoDossier() + "' and " + " PDNUM = '" + cle.getNumeroBon() + "' and PDREP = '"
							+ cle.getTypeLigne() + "' and PDVEH = '" + cle.getNoVehicule() + "'");
		} catch (Throwable e) {
			log.error(e);
		} finally {

		}
	}

	/***
	 * Méthode de mise d'un transfert OK.
	 * @param cle
	 */
	public void razTransfertOK() {
		try {
			getJdbcTemplate().update("UPDATE PDAD set PDTFT = '0' , PDETAT_TR = '', PDCOD_TR = '', PDLIB_TR = '', PDDAT_TR = '', PDDEB_ETAP ='', PDFIN_ETAP='', PDPALEC ='0',PDPALER ='0', PDPALDC ='0', PDPALDR ='0', PDCLIT = '', PDLLIT ='', PDSIGN = '', PDCOR='0', PDINFOM='0' ");
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
		}
	}
	
	/***
	 * Méthode de mise d'un transfert OK.
	 * @param cle
	 */
	public void executeRequete() {
		
		try {
			//getJdbcTemplate().update("UPDATE PDAD set PDNUMSEQ = '1', PDNOME='ROQUETTE', PDNOML = 'MOUSSY' where PDNUM = '575985' and PDREP='2' and PDCD='D' ");
			getJdbcTemplate().update("UPDATE PDAD set PDMS = '0', PDTFT = '0'");		
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
		}
	}

	public void deleteTest() {
		try {
			getJdbcTemplate().update("UPDATE PDAD set PDMS = '0', PDPDU = 'N', PDNCOM = 'O' , PDTFT = '0' WHERE PDNUM = '563128' and PDVEH = '1567'");
			// getJdbcTemplate().update("UPDATE PDAD set PDTFT = '0' WHERE PDNUM = '563128' and PDVEH = '3245'");
		} catch (Throwable e) {
			e.printStackTrace();
		} finally {
		}
	}

	/**
	 * Récupération des informations du client.
	 * @param noClient le numéro client
	 * @param dossier le dossier du client
	 * @return {@link Client}
	 */
	public Tiers recupererClientTableClient(String noClient, String dossier, Logger log) {
		Tiers tiers = null;
		try {
			SqlRowSet rs = getJdbcTemplate().queryForRowSet("SELECT * FROM CLIENT WHERE  CODCLI = '" + noClient + "' and CODSOC = '" + dossier + "'");
			while (rs.next()) {
				tiers = new Tiers();
				tiers.setNumeroTiers(rs.getString("CODCLI").trim());
				tiers.setCodeTiers(rs.getString("CODCLI").trim());
				tiers.setNom(rs.getString("NOMCLI").replaceAll("  ", " ").trim());
				tiers.setRaisonSociale(rs.getString("RASCLI").trim());
				tiers.setTelephone(rs.getString("TEL1CLI").trim());
				tiers.setAdresse1(rs.getString("AD1CLI").trim());
				tiers.setAdresse2(rs.getString("AD2CLI").trim());
				tiers.setCodePostale(rs.getString("CPOCLI").trim());
				tiers.setVille(rs.getString("VILCLI").trim());
				tiers.setPays(rs.getString("PAYS").trim());
			}
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}

		return tiers;
	}

	/**
	 * Mise à jour de la date et heure de début dans la table pdad
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 */
	public void updateDateHeureDebutBon(String dossier, String bonTransport, String typeLigne, String vehicule, String date, Logger log) {
		try {
			String pdharr = date.substring(8, 12);
			String requete = "UPDATE PDAD set PDDEB_ETAP = '" + date + "', PDHRARR = '" + pdharr + "' where PDDOS = '" + dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "'";
			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

	/**
	 * Mise à jour de la date et heure de passage à quai dans la table pdad
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 */
	public void updateDateHeureAQuaiBon(String dossier, String bonTransport, String typeLigne, String vehicule, String date, Logger log) {
		try {
			String PDHRCHARG = date.substring(8, 12);
			String requete = "UPDATE PDAD set PDHRCHARG = '" + PDHRCHARG + "' where PDDOS = '" + dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "'";
			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

	/**
	 * Mise à jour de la date et heure de fin dans la table pdad
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 */
	public void updateDateHeureDebutFin(String dossier, String bonTransport, String typeLigne, String vehicule, String date, Logger log) {
		try {
			String PDHRDEP = date.substring(8, 12);
			String requete = "UPDATE PDAD set PDFIN_ETAP = '" + date + "', PDHRDEP = '" + PDHRDEP + "' where PDDOS = '" + dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "'";
			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

	/**
	 * Mise à jour des scan, photo et signature d'un bon
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 * @param codeBarre
	 * @param photo
	 * @param signature
	 */
	public void updateReponseAction(String dossier, String bonTransport, String typeLigne, String vehicule, String codeBarre, String photo, String signature, String nbPaletteERendu, String nbPaletteECharger, String nbPaletteARendu, String nbPaletteACharger, Logger log) {
		try {
			String requete = "UPDATE PDAD set PDPALEC = " + nbPaletteECharger + " , PDPALER = " + nbPaletteERendu + " , PDPALDC = " + nbPaletteACharger + " , PDPALDR = " + nbPaletteARendu + " , PDSIGN = '" + signature + "', PDCBARR = '" + codeBarre + "', PDPHOT = '" + photo + "' where PDDOS = '"
					+ dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "'";

			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

	/**
	 * Mise à jour de la quantité modifié par le chauffeur.
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 * @param unite
	 */
	public void updateQuantie(String dossier, String bonTransport, String typeLigne, String vehicule, String unite, String typeAction, Logger log) {
		try {
			String requete = "UPDATE PDAD set PDCOR = '1', PDINFOM = '" + unite + "' where PDDOS = '" + dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "' and PDCD = '" + typeAction + "'";
			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

	/**
	 * Mise à jour des scan, photo et signature d'un bon
	 * @param dossier
	 * @param bonTransport
	 * @param typeLigne
	 * @param vehicule
	 * @param codeBarre
	 * @param photo
	 * @param signature
	 */
	public void updateLitigeAction(String dossier, String bonTransport, String typeLigne, String vehicule, String codeLitige, String libelleLitige, Logger log) {
		try {
			String requete = "UPDATE PDAD set PDCLIT = '" + codeLitige + "', PDLLIT = '" + libelleLitige.replaceAll("'", " ") + "' where PDDOS = '" + dossier + "' and PDNUM = '" + bonTransport + "' and PDREP = '" + typeLigne + "'";
			getJdbcTemplate().update(requete);
		} catch (Throwable e) {
			log.error(e);
		} finally {
		}
	}

}
